import pandas as pd
import mysql_util as sql_util
import json
import math

"""查询用户是否分配配权限"""
if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/20240326-174619.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)

    # 打印读取到的数据
    # print(df)
    print(type(df))
    mobiles = []
    flags = []
    for index, row in df.iterrows():
        mobile = row['手机号']
        mobiles.append(mobile)
        flag = '否'
        if math.isnan(mobile):
            # mobile = 0
            print('行号%d手机号有问题', (index))
        try:
            mobile = str(int(mobile))
            # print(type(mobile))
            print(mobile)
            # 查询用户是否绑定了权限
            sql = "select count(1) num from pc_user_organization uo where uo.user_id in ( \
                    select id from pc_user u where mobile = '{}' and u.enabled =1 \
                    )".format(mobile)
            sql = "select sum(num) num from (\
                    select count(1) num from pc_user_organization uo where uo.user_id in (\
                    select id from pc_user u where mobile = '{}' and u.enabled =1\
                    )\
                    union all\
                    select count(1) num from bae_organization_user uo where uo.external_id in (\
                    select u.external_id from pc_user u where mobile = '{}' and u.enabled =1\
                    )\
                    ) a".format(mobile, mobile)
            rs = sql_util.select_by_sql(sql)
            rs = json.loads(rs)
            print(rs)
            num = int(rs[0]['num'])
            if num > 0:
                flag = '是'
        except Exception as e:
            print(e)
            print(index)

        flags.append(flag)
    excel_data = {}
    excel_data['手机号'] = mobiles
    excel_data['是否上养殖系统'] = flags
    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + 'test.xlsx', index=False)
